/**
 * Company : Thai Informatic System Co., Ltd.
 * Created By : Dr.Tree
 * Created On : Jun 28, 2012  12:24:32 AM 
 * e-Mail : chatree@tis.co.th
 * Mobile Phone : (+66) 087-921-5115
 * Package Name : jpa.service.synchro.staging.log.service
 * File Name : StagingObject.java
 * TODO
 */
package synchro.staging.log.action;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;

import jpa.entitys.synchro.staging.logs.VwUserColumn;
import jpa.service.synchro.staging.log.VwUserColumnServices;

import org.apache.log4j.Logger;

import promis.db.connection.dbHelper;
import promis.utility.CanlendarDateFormat;
import promis.utility.CommonService;
import promis.utility.Utilitys;
import apps.sessionFactory;

/**
 * 
 * @author Chatree Siangklom of Administrator
 * Date =Nov 13, 2013 : 3:02:39 PM 
 * CreateBy=Chatree Siangklom
 * 
 * StagingObject
 * TODO
 */
public class StagingObject {

	private static final Logger log = Logger.getLogger(StagingObject.class);
	private EntityManager emSynchro;

	/**
	 * @throws Exception 
	 * 
	 */
	public StagingObject() throws Exception {
		emSynchro =  sessionFactory.CreateEmSynchro();
	}
	
	public List<VwUserColumn> getUserAllColumns() throws Exception 
	{
		try{
			
			VwUserColumnServices VwUserColumnServ = new VwUserColumnServices(emSynchro);
			
			return VwUserColumnServ.findAll();
			
		}catch(Exception e){
			log.error(e.getMessage());
			System.err.println(e.getMessage());
			throw e;
		}
	}
	
	/**
	 * 
	 * Date =Nov 13, 2013 : 3:02:43 PM 
	 * CreateBy=Chatree Siangklom
	 * List<VwUserColumn>
	 * TODO
	 * @param tableName
	 * @return
	 * @throws Exception
	 */
	public List<VwUserColumn> getUserColumnsByTable(String tableName) throws Exception 
	{
		try{
			
			VwUserColumnServices VwUserColumnServ = new VwUserColumnServices(emSynchro);
			
			return VwUserColumnServ.findByTable(tableName);
			
		}catch(Exception e){
			log.error(e.getMessage());
			System.err.println(e.getMessage());
			throw e;
		}
	}
	
	/**
	 * 
	 * Date =Nov 13, 2013 : 3:02:45 PM 
	 * CreateBy=Chatree Siangklom
	 * void
	 * TODO
	 * @param tableName
	 * @throws Exception
	 */
	private void Columns(String tableName) throws Exception {
		try{
			
			java.sql.Statement stmt = new dbHelper().getConn().createStatement();
			ResultSet rs = stmt.executeQuery("");
			
		}catch(Exception e){			
			log.error(e.getMessage());
			System.err.println(e.getMessage());
			throw e;
		}
	}
	
	/**
	 * 
	 * Date =Nov 13, 2013 : 3:02:49 PM 
	 * CreateBy=Chatree Siangklom
	 * String
	 * TODO
	 * @param tableName
	 * @return
	 * @throws Exception
	 */
	private String buildingSql(String tableName) throws Exception {
		try{
		
			StringBuffer _strQuery = new StringBuffer();
			
			StagingObject StagingObj = new StagingObject();
			List<VwUserColumn> lst1 = StagingObj.getUserColumnsByTable(tableName);
			if(!CommonService.IsNull(lst1)){
				
				_strQuery.append("  ");
				
				for(VwUserColumn value : lst1)
				{
					//Columns
					List<VwUserColumn> columns = StagingObj.getUserColumnsByTable(value.getId().getTableName());
					if(!CommonService.IsNull(columns)){
						
						for(VwUserColumn col : columns){
							log.info("	ColumnName = " + col.getId().getColumnName());
							
							
						}
							
					}
				}
			}
			
		}catch(Exception e){
			throw e;
		}
		
		return null;
	}
	
	private boolean IsTable(String TABLE_NAME) throws Exception {
		Connection conn = new dbHelper().getConn();
		Statement stmt = conn.createStatement();				
		try{
			String sql = " select * from SYS.USER_TABLES Where TABLE_NAME  = " + Utilitys.strSql(TABLE_NAME);
			ResultSet rs = stmt.executeQuery(sql);
			
			if(rs != null){
				while(rs.next()){
					if(rs.getString("TABLE_NAME").toUpperCase().equals(CommonService.IsNullRString(TABLE_NAME).toUpperCase())){
						return true;
					}	
				}				
			}	
			
			rs.close();
			
		}catch(Exception e){
			System.out.println(e.getMessage());
			log.error(e.getMessage());
		}finally{
			stmt.close();
            conn.close();
		}
		return false;
	}
	
	/**
	 * 
	 * Date =Nov 13, 2013 : 3:02:54 PM 
	 * CreateBy=Chatree Siangklom
	 * void
	 * TODO
	 * @throws Exception
	 */
	private void CreateTableMonthLogs() throws Exception {
		try
		{
			Date date = Calendar.getInstance().getTime();
			String dateTable = (Integer.parseInt(CommonService.ConvertDateToSAPStringFormat(date, CanlendarDateFormat.DATE_FORMAT_FULLDATE_ONLY_SAP_YEAR_MONTH))-1)+"";
			
			if(IsTable("LOGS_INBOUND_"+ dateTable +"") == false && IsTable("LOGS_OUTBOUND_"+ dateTable +"") == false)
			{
				Connection conn = new dbHelper().getConn();
				Statement stmt = conn.createStatement();
				StringBuilder _strQuery = new StringBuilder();
				
				_strQuery = new StringBuilder();
				_strQuery.append(" CREATE TABLE SYNCHRO.LOGS_INBOUND_"+ dateTable +" ");
				_strQuery.append(" ( ");
				_strQuery.append(" LOGS_ID    NUMBER                             NOT NULL, ");
				_strQuery.append(" USER_ID    VARCHAR2(500 BYTE)                 NOT NULL, ");
				_strQuery.append(" DATED      DATE                               NOT NULL, ");
				_strQuery.append(" LOGGER     VARCHAR2(3700 BYTE)                NOT NULL, ");
				_strQuery.append(" \"LEVEL\"    VARCHAR2(500 BYTE)               NOT NULL, ");
				_strQuery.append(" MESSAGE    VARCHAR2(3700 BYTE)                NOT NULL, ");
				_strQuery.append(" CREATEDBY  VARCHAR2(500 BYTE), ");
				_strQuery.append(" CREATEDON  DATE                               DEFAULT current_date, ");
				_strQuery.append(" UPDATEDBY  VARCHAR2(500 BYTE), ");
				_strQuery.append(" UPDATEDON  DATE                               DEFAULT current_date ");
				_strQuery.append(" ) ");
				_strQuery.append(" TABLESPACE USERS ");
				_strQuery.append(" PCTUSED    0 ");
				_strQuery.append(" PCTFREE    10 ");
				_strQuery.append(" INITRANS   1 ");
				_strQuery.append(" MAXTRANS   255 ");
				_strQuery.append(" STORAGE    ( ");
				_strQuery.append(" INITIAL          64K ");
				_strQuery.append(" NEXT             1M ");
				_strQuery.append(" MINEXTENTS       1 ");
				_strQuery.append(" MAXEXTENTS       UNLIMITED ");
				_strQuery.append(" PCTINCREASE      0 ");
				_strQuery.append(" BUFFER_POOL      DEFAULT ");
				_strQuery.append(" ) ");
				_strQuery.append(" LOGGING  ");
				_strQuery.append(" NOCOMPRESS  ");
				_strQuery.append(" NOCACHE ");
				_strQuery.append(" NOPARALLEL ");
				_strQuery.append(" MONITORING ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());

				_strQuery = new StringBuilder();
				_strQuery.append(" CREATE UNIQUE INDEX SYNCHRO.LOGSINB"+ dateTable +" _"+ dateTable +" _PK ON SYNCHRO.LOGS_INBOUND_"+ dateTable +"  ");
				_strQuery.append(" (LOGS_ID) ");
				_strQuery.append(" LOGGING ");
				_strQuery.append(" TABLESPACE USERS ");
				_strQuery.append(" PCTFREE    10 ");
				_strQuery.append(" INITRANS   2 ");
				_strQuery.append(" MAXTRANS   255 ");
				_strQuery.append(" STORAGE    ( ");
				_strQuery.append(" INITIAL          64K ");
				_strQuery.append(" NEXT             1M ");
				_strQuery.append(" MINEXTENTS       1 ");
				_strQuery.append(" MAXEXTENTS       UNLIMITED ");
				_strQuery.append(" PCTINCREASE      0 ");
				_strQuery.append(" BUFFER_POOL      DEFAULT ");
				_strQuery.append(" ) ");
				_strQuery.append(" NOPARALLEL ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());
				
				_strQuery = new StringBuilder();
				_strQuery.append(" CREATE INDEX SYNCHRO.LOGSINB"+ dateTable +" _LOGGER_INDX ON SYNCHRO.LOGS_INBOUND_"+ dateTable +"  ");
				_strQuery.append(" (LOGGER) ");
				_strQuery.append(" LOGGING ");
				_strQuery.append(" TABLESPACE USERS ");
				_strQuery.append(" PCTFREE    10 ");
				_strQuery.append(" INITRANS   2 ");
				_strQuery.append(" MAXTRANS   255 ");
				_strQuery.append(" STORAGE    ( ");
				_strQuery.append(" INITIAL          64K ");
				_strQuery.append(" NEXT             1M ");
				_strQuery.append(" MINEXTENTS       1 ");
				_strQuery.append(" MAXEXTENTS       UNLIMITED ");
				_strQuery.append(" PCTINCREASE      0 ");
				_strQuery.append(" BUFFER_POOL      DEFAULT ");
				_strQuery.append(" ) ");
				_strQuery.append(" NOPARALLEL ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());

				_strQuery = new StringBuilder();
				_strQuery.append(" CREATE INDEX SYNCHRO.LOGSINB"+ dateTable +" _MESSAGE_INDX ON SYNCHRO.LOGS_INBOUND_"+ dateTable +"  ");
				_strQuery.append(" (MESSAGE) ");
				_strQuery.append(" LOGGING ");
				_strQuery.append(" TABLESPACE USERS ");
				_strQuery.append(" PCTFREE    10 ");
				_strQuery.append(" INITRANS   2 ");
				_strQuery.append(" MAXTRANS   255 ");
				_strQuery.append(" STORAGE    ( ");
				_strQuery.append(" INITIAL          64K ");
				_strQuery.append(" NEXT             1M ");
				_strQuery.append(" MINEXTENTS       1 ");
				_strQuery.append(" MAXEXTENTS       UNLIMITED ");
				_strQuery.append(" PCTINCREASE      0 ");
				_strQuery.append(" BUFFER_POOL      DEFAULT ");
				_strQuery.append(" ) ");
				_strQuery.append(" NOPARALLEL ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());

				_strQuery = new StringBuilder();
				_strQuery.append(" ALTER TABLE SYNCHRO.LOGS_INBOUND_"+ dateTable +"  ADD ( ");
				_strQuery.append(" CONSTRAINT LOGS_INBOUND_"+ dateTable +" _PK ");
				_strQuery.append(" PRIMARY KEY ");
				_strQuery.append(" (LOGS_ID) ");
				_strQuery.append(" USING INDEX SYNCHRO.LOGSINB"+ dateTable +" _"+ dateTable +" _PK) ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());
				
				_strQuery = new StringBuilder();
				_strQuery.append(" GRANT DELETE, INSERT, SELECT, UPDATE ON SYNCHRO.LOGS_INBOUND_"+ dateTable +"  TO ALL_SPL_OBJECT ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());
				
				_strQuery = new StringBuilder();
				_strQuery.append(" CREATE TABLE SYNCHRO.LOGS_OUTBOUND_"+ dateTable +"  ");
				_strQuery.append(" ( ");
				_strQuery.append(" LOGS_ID    NUMBER                             NOT NULL, ");
				_strQuery.append(" USER_ID    VARCHAR2(500 BYTE)                 NOT NULL, ");
				_strQuery.append(" DATED      DATE                               NOT NULL, ");
				_strQuery.append(" LOGGER     VARCHAR2(3700 BYTE)                NOT NULL, ");
				_strQuery.append(" \"LEVEL\"    VARCHAR2(500 BYTE)               NOT NULL, ");
				_strQuery.append(" MESSAGE    VARCHAR2(3700 BYTE)                NOT NULL, ");
				_strQuery.append(" CREATEDBY  VARCHAR2(500 BYTE), ");
				_strQuery.append(" CREATEDON  DATE                               DEFAULT current_date, ");
				_strQuery.append(" UPDATEDBY  VARCHAR2(500 BYTE), ");
				_strQuery.append(" UPDATEDON  DATE                               DEFAULT current_date ");
				_strQuery.append(" ) ");
				_strQuery.append(" TABLESPACE USERS ");
				_strQuery.append(" PCTUSED    0 ");
				_strQuery.append(" PCTFREE    10 ");
				_strQuery.append(" INITRANS   1 ");
				_strQuery.append(" MAXTRANS   255 ");
				_strQuery.append(" STORAGE    ( ");
				_strQuery.append(" INITIAL          64K ");
				_strQuery.append(" NEXT             1M ");
				_strQuery.append(" MINEXTENTS       1 ");
				_strQuery.append(" MAXEXTENTS       UNLIMITED ");
				_strQuery.append(" PCTINCREASE      0 ");
				_strQuery.append(" BUFFER_POOL      DEFAULT ");
				_strQuery.append(" ) ");
				_strQuery.append(" LOGGING  ");
				_strQuery.append(" NOCOMPRESS "); 
				_strQuery.append(" NOCACHE ");
				_strQuery.append(" NOPARALLEL ");
				_strQuery.append(" MONITORING ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());

				_strQuery = new StringBuilder();
				_strQuery.append(" CREATE INDEX SYNCHRO.LOGS_OUTBOUND_"+ dateTable +" MSGINDX ON SYNCHRO.LOGS_OUTBOUND_"+ dateTable +"  ");
				_strQuery.append(" (MESSAGE) ");
				_strQuery.append(" LOGGING ");
				_strQuery.append(" TABLESPACE USERS ");
				_strQuery.append(" PCTFREE    10 ");
				_strQuery.append(" INITRANS   2 ");
				_strQuery.append(" MAXTRANS   255 ");
				_strQuery.append(" STORAGE    ( ");
				_strQuery.append(" INITIAL          64K ");
				_strQuery.append(" NEXT             1M ");
				_strQuery.append(" MINEXTENTS       1 ");
				_strQuery.append(" MAXEXTENTS       UNLIMITED ");
				_strQuery.append(" PCTINCREASE      0 ");
				_strQuery.append(" BUFFER_POOL      DEFAULT ");
				_strQuery.append(" ) ");
				_strQuery.append(" NOPARALLEL ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());

				_strQuery = new StringBuilder();
				_strQuery.append(" CREATE INDEX SYNCHRO.LOGS_OUTBOUND_"+ dateTable +" _INDX ON SYNCHRO.LOGS_OUTBOUND_"+ dateTable +"  ");
				_strQuery.append(" (LOGGER) ");
				_strQuery.append(" LOGGING ");
				_strQuery.append(" TABLESPACE USERS ");
				_strQuery.append(" PCTFREE    10 ");
				_strQuery.append(" INITRANS   2 ");
				_strQuery.append(" MAXTRANS   255 ");
				_strQuery.append(" STORAGE    ( ");
				_strQuery.append(" INITIAL          64K ");
				_strQuery.append(" NEXT             1M ");
				_strQuery.append(" MINEXTENTS       1 ");
				_strQuery.append(" MAXEXTENTS       UNLIMITED ");
				_strQuery.append(" PCTINCREASE      0 ");
				_strQuery.append(" BUFFER_POOL      DEFAULT ");
				_strQuery.append(" ) ");
				_strQuery.append(" NOPARALLEL ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());

				_strQuery = new StringBuilder();
				_strQuery.append(" CREATE UNIQUE INDEX SYNCHRO.LOGS_OUTBOUND_"+ dateTable +" _PK ON SYNCHRO.LOGS_OUTBOUND_"+ dateTable +"  ");
				_strQuery.append(" (LOGS_ID) ");
				_strQuery.append(" LOGGING ");
				_strQuery.append(" TABLESPACE USERS ");
				_strQuery.append(" PCTFREE    10 ");
				_strQuery.append(" INITRANS   2 ");
				_strQuery.append(" MAXTRANS   255 ");
				_strQuery.append(" STORAGE    ( ");
				_strQuery.append(" INITIAL          64K ");
				_strQuery.append(" NEXT             1M ");
				_strQuery.append(" MINEXTENTS       1 ");
				_strQuery.append(" MAXEXTENTS       UNLIMITED ");
				_strQuery.append(" PCTINCREASE      0 ");
				_strQuery.append(" BUFFER_POOL      DEFAULT ");
				_strQuery.append(" ) ");
				_strQuery.append(" NOPARALLEL ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());
				
				_strQuery = new StringBuilder();
				_strQuery.append(" ALTER TABLE SYNCHRO.LOGS_OUTBOUND_"+ dateTable +"  ADD ( ");
				_strQuery.append(" CONSTRAINT LOGS_OUTBOUND_"+ dateTable +" _PK ");
				_strQuery.append(" PRIMARY KEY ");
				_strQuery.append(" (LOGS_ID) ");
				_strQuery.append(" USING INDEX SYNCHRO.LOGS_OUTBOUND_"+ dateTable +" _PK) ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());
				
	            // Executing executeBatch
	            stmt.executeBatch();
	            stmt.close();
	            conn.close();	
			}else{
				System.out.println("Else Have Tabls");
			}
		}catch(SQLException e){
			log.error(e.getMessage());
			System.out.println(e.getMessage());
		}catch(Exception e){
			log.error(e.getMessage());
			System.out.println(e.getMessage());
		}
	}
	
	/**
	 * 
	 * Date =Nov 13, 2013 : 3:03:01 PM 
	 * CreateBy=Chatree Siangklom
	 * void
	 * TODO
	 * @throws Exception
	 */
	private void MoveMonthLogs() throws Exception {
		try
		{
			Date date = Calendar.getInstance().getTime();
			String dateTable = (Integer.parseInt(CommonService.ConvertDateToSAPStringFormat(date, CanlendarDateFormat.DATE_FORMAT_FULLDATE_ONLY_SAP_YEAR_MONTH))-1)+"";
			
			Connection conn = new dbHelper().getConn();
			Statement stmt = conn.createStatement();
			
			StringBuilder _strQuery = new StringBuilder();
			_strQuery.append(" insert into LOGS_OUTBOUND_"+ dateTable +" value select * from LOGS_OUTBOUND where to_char(DATED,'YYYYMMDD') <=  TO_CHAR(CURRENT_DATE -1,'YYYYMMDD') ");
			System.out.println(_strQuery.toString());
			stmt.addBatch(_strQuery.toString());			
			 
			_strQuery = new StringBuilder();
			_strQuery.append(" delete from LOGS_OUTBOUND where to_char(DATED,'YYYYMMDD') <= TO_CHAR(CURRENT_DATE -1,'YYYYMMDD') ");
			System.out.println(_strQuery.toString());
			stmt.addBatch(_strQuery.toString());
			
			_strQuery = new StringBuilder();
			_strQuery.append(" insert into LOGS_INBOUND_"+ dateTable +" value select * from LOGS_INBOUND where to_char(DATED,'YYYYMMDD') <= TO_CHAR(CURRENT_DATE -1,'YYYYMMDD') ");
			System.out.println(_strQuery.toString());
			stmt.addBatch(_strQuery.toString());
			
			_strQuery = new StringBuilder();
			_strQuery.append(" delete from LOGS_INBOUND where to_char(DATED,'YYYYMMDD') <= TO_CHAR(CURRENT_DATE -1,'YYYYMMDD') ");
			System.out.println(_strQuery.toString());
			stmt.addBatch(_strQuery.toString());			
			
            // Executing executeBatch
            stmt.executeBatch();
            stmt.close();
            conn.close();
            
		}catch(SQLException e){
			log.error(e.getMessage());
			System.out.println(e.getMessage());
		}catch(Exception e){
			log.error(e.getMessage());
			System.out.println(e.getMessage());
		}
	}
	
	
	public static void main(String[] args) {
	
		Date date = Calendar.getInstance().getTime();
		System.out.println(Integer.parseInt(CommonService.ConvertDateToSAPStringFormat(date, CanlendarDateFormat.DATE_FORMAT_FULLDATE_ONLY_SAP_YEAR_MONTH))-1);
		try {
			
			new StagingObject().CreateTableMonthLogs();
			
			new StagingObject().MoveMonthLogs();
			
		} catch (Exception e) {
			log.error(e.getMessage());
			System.err.println(e.getMessage());
		}
	}
	

	/**
	 * @param args
	 */
//	public static void main(String[] args) {
//		// TODO Auto-generated method stub
//		try {
//			
//			StagingObject StagingObj = new StagingObject();
//			
//			/**
//			List<VwUserColumn> lst = StagingObj.getUserAllColumns();
//			if(!CommonService.IsNull(lst)){
//				log.info(lst.size());
//				for(VwUserColumn value : lst)
//				{
//					log.info("ColumnId = " + value.getColumnId() + ", ColumnName = " + value.getId().getColumnName());
//				}
//			}else 
//				log.info(" ELSE ");
//			
//			*/
//			
//			List<VwUserTable> lst1 = new StagingObject().getUserAllTables();
//			if(!CommonService.IsNull(lst1)){
//				
//				for(VwUserTable value : lst1)
//				{
//					log.info("TablespaceName = " + value.getId().getTablespaceName() + ", TableName = " + value.getId().getTableName());
//					
//					//Columns
//					List<VwUserColumn> columns = StagingObj.getUserColumnsByTable(value.getId().getTableName());
//					if(!CommonService.IsNull(columns)){
//						
//						for(VwUserColumn col : columns)
//							log.info("	ColumnName = " + col.getId().getColumnName());
//					}
//				}
//			}else 
//				log.info(" ELSE ");
//
//		} catch (Exception e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//	}
}
